﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.Collections;
using Cashier_Model;
using System.Reflection;
using Cashier_Utils;
using System.Data.Common;
using MySql.Data.MySqlClient;
using System.Data.SqlClient;

namespace Cashier_DAO.db
{
    public class DAOFactory
    {
        /// <summary>
        /// SQL参数化
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="key"></param>
        /// <param name="value"></param>
        private void AddCommandParamters(DbCommand cmd, String key, Object value)
        {
            try
            {
                String typename = value.GetType().Name.ToLower();
                DbType dbtype = DbType.String;
                switch (typename)
                {
                    case "int64": dbtype = DbType.Int64;
                        break;
                    case "int32": dbtype = DbType.Int32;
                        break;
                    case "int16": dbtype = DbType.Int16;
                        break;
                    case "string": dbtype = DbType.String;
                        break;
                    case "datetime": dbtype = DbType.DateTime;
                        break;
                    case "decimal": dbtype = DbType.Decimal;
                        break;
                    case "boolean": dbtype = DbType.Boolean;
                        break;
                }
                if (ConnectionFactory.DatabaseType == DatabaseType.SQLite)
                {
                    SQLiteCommand sqliteCommand = (SQLiteCommand)cmd;
                    sqliteCommand.Parameters.Add("@" + key, dbtype);
                    sqliteCommand.Parameters["@" + key].Value = value;
                }
                else if (ConnectionFactory.DatabaseType == DatabaseType.MySQL)
                {
                    MySqlCommand mysqlCommand = (MySqlCommand)cmd;
                    mysqlCommand.Parameters.AddWithValue("@" + key, value);
                    //mysqlCommand.Parameters["@" + key].Value = value;
                }
                else if (ConnectionFactory.DatabaseType == DatabaseType.SQLServer)
                {
                    SqlCommand sqlCommand = (SqlCommand)cmd;
                    sqlCommand.Parameters.AddWithValue("@" + key, value);
                    //sqlCommand.Parameters["@" + key].Value = value;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 参数化SQL
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="paramtable"></param>
        protected void AddCommandParamters(DbCommand cmd, Hashtable paramtable)
        {
            try
            {
                if (paramtable != null && paramtable.Count > 0)
                {
                    IEnumerator iter = paramtable.Keys.GetEnumerator();
                    while (iter.MoveNext())
                    {
                        String key = iter.Current.ToString();
                        Object value = paramtable[key];
                        this.AddCommandParamters(cmd, key, value);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 处理查询条件
        /// </summary>
        /// <param name="sb"></param>
        /// <param name="paramtable"></param>
        /// <param name="model"></param>
        protected void AddParameters(StringBuilder sb, Hashtable paramtable, Object model)
        {
            if (model != null)
            {
                PropertyInfo[] properties = model.GetType().GetProperties();
                foreach (PropertyInfo lopProperty in properties)
                {
                    if (lopProperty.GetValue(model, null) != null)
                    {                        
                        Attribute[] attrs = Attribute.GetCustomAttributes(lopProperty);
                        if (attrs != null && attrs.Length > 0)
                        {
                            TableAttribute tableAttribute = (TableAttribute)attrs[0];
                            String columnname = tableAttribute.Columnname;
                            bool iscondition = tableAttribute.Iscondition;
                            //是查询条件
                            if (iscondition)
                            {
                                String fieldType = lopProperty.PropertyType.Name.ToLower();
                                String fieldValue = lopProperty.GetValue(model, null).ToString();
                                if (fieldType.IndexOf("int") >= 0)
                                {
                                    if (fieldValue.Equals("0"))
                                        continue;
                                }
                                else if (fieldType.Equals("string"))
                                {
                                    if (String.IsNullOrEmpty(fieldValue))
                                        continue;
                                }
                                else if (fieldType.Equals("decimal"))
                                {
                                    if (Decimal.Parse(fieldValue) <= 0)
                                        continue;
                                }
                                else if (fieldType.IndexOf("nullable") == 0)
                                {
                                    if (fieldValue == null)
                                        continue;
                                }

                                ConditionType conditiontype = tableAttribute.Conditiontype;
                                String fieldothername = tableAttribute.Fieldothername;

                                String prev_columnname = columnname;
                                if (!String.IsNullOrEmpty(fieldothername))
                                    prev_columnname = fieldothername + "." + prev_columnname;

                                Object value = lopProperty.GetValue(model, null);
                                if (conditiontype == ConditionType.EQUAL)
                                {
                                    sb.Append(" and ").Append(prev_columnname).Append("=@").Append(columnname);
                                    paramtable.Add(columnname, value);
                                }
                                else if (conditiontype == ConditionType.LIKE)
                                {
                                    sb.Append(" and (").Append(prev_columnname).Append(" like @").Append(columnname).Append(")");
                                    paramtable.Add(columnname, "%" + value + "%");
                                }
                                else if (conditiontype == ConditionType.LIKE_PREFIX)
                                {
                                    sb.Append(" and (").Append(prev_columnname).Append(" like @").Append(columnname).Append(")");
                                    paramtable.Add(columnname, "%" + value);
                                }
                                else if (conditiontype == ConditionType.LIKE_SUFFIX)
                                {
                                    sb.Append(" and (").Append(prev_columnname).Append(" like @").Append(columnname).Append(")");
                                    paramtable.Add(columnname, value + "%");
                                }
                                else if (conditiontype == ConditionType.GREATER)
                                {
                                    bool isAppend = false;
                                    if (tableAttribute.Compareto != null)
                                    {
                                        String valuetype = value.GetType().Name.ToLower();
                                        if (valuetype == "int32" && ((int)value > (int)tableAttribute.Compareto))
                                            isAppend = true;
                                        if (valuetype == "decimal" && (Decimal.Parse(value.ToString()) > Decimal.Parse(tableAttribute.Compareto.ToString())))
                                            isAppend = true;
                                    }
                                    else
                                        isAppend = true;
                                    if (isAppend)
                                    {
                                        sb.Append(" and ").Append(prev_columnname).Append(">@").Append(columnname).Append("");
                                        paramtable.Add(columnname, value);
                                    }
                                }
                                else if (conditiontype == ConditionType.LESS)
                                {
                                    if ((int)value > 0)
                                    {
                                        sb.Append(" and ").Append(prev_columnname).Append("<@").Append(columnname);
                                        paramtable.Add(columnname, value);
                                    }
                                }
                                /*
                                else if (conditiontype == ConditionType.BOOLEAN && (bool)value)
                                {
                                    sb.Append(" and ").Append(prev_columnname).Append("=@").Append(columnname).Append("");
                                    paramtable.Add(columnname, value);
                                }
                                else
                                {
                                    sb.Append(" and ").Append(prev_columnname).Append("=@").Append(columnname);
                                    paramtable.Add(columnname, value);
                                }
                                 * */
                            }
                        }
                    }
                }
                Console.WriteLine(sb.ToString());
            }
        }

        /// <summary>
        /// 生成排序及分页SQL
        /// </summary>
        /// <param name="sb"></param>
        /// <param name="paramtable"></param>
        /// <param name="model"></param>
        protected void GenerateOrderAndPageSQL(StringBuilder sb, Hashtable paramtable, BaseModel model)
        {
            try
            {
                String order = String.Empty;
                if (!String.IsNullOrEmpty(model.Ascfield))
                {
                    order = model.Ascfield + " asc";
                }
                if (!String.IsNullOrEmpty(model.Descfield))
                {
                    if (String.IsNullOrEmpty(order))
                        order = model.Descfield + " desc";
                    else
                        order += "," + model.Descfield + " desc";
                }
                if (!String.IsNullOrEmpty(order))
                    sb.Append(" order by ").Append(order);

                if (model.Pagesize > 0)
                {
                    sb.Append(" limit @offset,@pagesize");
                    paramtable.Add("offset", (model.Pageindex - 1) * model.Pagesize);
                    paramtable.Add("pagesize", model.Pagesize);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 将DataRow转化为实体对象
        /// </summary>
        /// <param name="row"></param>
        /// <param name="model"></param>
        public void DataRowToModel(DataRow row, Object model)
        {
            try
            {
                DataColumnCollection columns = row.Table.Columns;
                foreach (DataColumn lopColumn in columns)
                {
                    String columnname = lopColumn.ColumnName;
                    Object rowValue = row[columnname];
                    if (rowValue != null && rowValue!=DBNull.Value)
                    {
                        PropertyInfo[] properties = model.GetType().GetProperties();
                        foreach (PropertyInfo lopPropertyInfo in properties)
                        {
                            String propertyTypeName = lopPropertyInfo.PropertyType.Name.ToLower();
                            Attribute[] attributes = Attribute.GetCustomAttributes(lopPropertyInfo);
                            if (attributes != null && attributes.Length > 0)
                            {
                                TableAttribute tableAttribute = (TableAttribute)attributes[0];
                                if (tableAttribute.Columnname.Equals(columnname))
                                {
                                    if (ConnectionFactory.DatabaseType == DatabaseType.SQLite)
                                    {
                                        lopPropertyInfo.SetValue(model, row[columnname], null);
                                    }
                                    else if (ConnectionFactory.DatabaseType == DatabaseType.MySQL)
                                    {
                                        if (columnname.IndexOf("is_") == 0)
                                        {
                                            bool modelValue = false;
                                            if (row[columnname] != null && (row[columnname].ToString().Equals("1") || row[columnname].ToString().Equals("true")))
                                                modelValue = true;
                                            lopPropertyInfo.SetValue(model, modelValue, null);
                                        }
                                        else
                                            lopPropertyInfo.SetValue(model, row[columnname], null);
                                    }
                                    break;
                                }
                            }
                            else
                            {
                                if (columnname.Equals(lopPropertyInfo.Name.ToLower()))
                                {
                                    if (ConnectionFactory.DatabaseType == DatabaseType.SQLite)
                                    {
                                        lopPropertyInfo.SetValue(model, row[columnname], null);
                                    }
                                    else if (ConnectionFactory.DatabaseType == DatabaseType.MySQL)
                                    {
                                        if (columnname.IndexOf("is_") == 0)
                                        {
                                            bool modelValue = false;
                                            if (row[columnname] != null && row[columnname].ToString().Equals("1"))
                                                modelValue = true;
                                            lopPropertyInfo.SetValue(model, modelValue, null);
                                        }
                                        else
                                            lopPropertyInfo.SetValue(model, row[columnname], null);
                                    }
                                    break;
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 生成对象插入SQL
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="model"></param>
        private void GenerateInsertSQL(DbCommand cmd, Object model)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                Attribute[] classAttributes = Attribute.GetCustomAttributes(model.GetType());
                if (classAttributes != null && classAttributes.Length > 0)
                {
                    TableAttribute classTableAttribute = (TableAttribute)classAttributes[0];
                    sb.Append("insert into ").Append(classTableAttribute.Tablename).Append("(");
                    StringBuilder valuesSB = new StringBuilder("values(");
                    Hashtable htable = new Hashtable();

                    PropertyInfo[] properties = model.GetType().GetProperties();
                    if (properties != null && properties.Length > 0)
                    {
                        foreach (PropertyInfo lopProperty in properties)
                        {
                            if (lopProperty.GetValue(model, null) != null)
                            {
                                Attribute[] attrs = Attribute.GetCustomAttributes(lopProperty);
                                if (attrs != null && attrs.Length > 0)
                                {
                                    TableAttribute tableAttribute = (TableAttribute)attrs[0];
                                    if (!tableAttribute.Iskey && tableAttribute.Isinsertfield)
                                    {
                                        sb.Append(tableAttribute.Columnname).Append(",");
                                        valuesSB.Append("@").Append(tableAttribute.Columnname).Append(",");
                                        htable.Add(tableAttribute.Columnname, lopProperty.GetValue(model, null));
                                    }
                                }
                            }
                        }
                        sb.Remove(sb.Length - 1, 1);
                        valuesSB.Remove(valuesSB.Length - 1, 1);
                        valuesSB.Append(")");
                        sb.Append(") ").Append(valuesSB.ToString());

                        cmd.CommandText = sb.ToString();
                        this.AddCommandParamters(cmd, htable);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        protected Object Insert(DbConnection conn, Object model)
        {
            Object oid = null;
            try
            {
                DbCommand cmd = CommandFactory.CreateCommand();
                cmd.Connection = conn;
                this.GenerateInsertSQL(cmd, model);
                Console.WriteLine(cmd.CommandText);
                int flag = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                if (flag > 0)
                {
                    if (ConnectionFactory.DatabaseType == DatabaseType.SQLite)
                        cmd.CommandText = "select last_insert_rowid()";
                    else if (ConnectionFactory.DatabaseType == DatabaseType.MySQL)
                        cmd.CommandText = "select last_insert_id()";
                    else if (ConnectionFactory.DatabaseType == DatabaseType.SQLServer)
                        cmd.CommandText = "select IDENT_CURRENT('')";
                    oid = cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return oid;
        }

        /// <summary>
        /// 生成更新SQL
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="model"></param>
        private void GenerateUpdateSQL(DbCommand cmd, Object model)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                Attribute[] classAttributes = Attribute.GetCustomAttributes(model.GetType());
                if (classAttributes != null && classAttributes.Length > 0)
                {
                    TableAttribute classTableAttribute = (TableAttribute)classAttributes[0];
                    sb.Append("update ").Append(classTableAttribute.Tablename).Append(" set ");
                    Hashtable htable = new Hashtable();

                    String where = String.Empty;
                    PropertyInfo[] properties = model.GetType().GetProperties();
                    if (properties != null && properties.Length>0)
                    {
                        foreach (PropertyInfo lopProperty in properties)
                        {
                            if (lopProperty.GetValue(model, null) != null)
                            {
                                Attribute[] fieldAttrs = Attribute.GetCustomAttributes(lopProperty);
                                if (fieldAttrs != null && fieldAttrs.Length > 0)
                                {
                                    TableAttribute fieldAttribute = (TableAttribute)fieldAttrs[0];
                                    String columnname = fieldAttribute.Columnname;
                                    if (fieldAttribute.Iskey)
                                    {
                                        where = " where " + columnname + "=@" + columnname;
                                        htable.Add(columnname, lopProperty.GetValue(model, null));
                                    }
                                    if (fieldAttribute.Isupdatefield)
                                    {
                                        sb.Append(columnname).Append("=@").Append(columnname).Append(",");
                                        htable.Add(columnname, lopProperty.GetValue(model, null));
                                    }
                                }
                            }
                        }
                        sb.Remove(sb.Length - 1, 1);
                        sb.Append(where);

                        cmd.CommandText = sb.ToString();
                        this.AddCommandParamters(cmd, htable);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        protected int Update(DbConnection conn, Object model)
        {
            int flag = 0;
            try
            {
                DbCommand cmd = CommandFactory.CreateCommand();
                cmd.Connection = conn;
                this.GenerateUpdateSQL(cmd, model);
                Console.WriteLine(cmd.CommandText);
                flag = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        /// <summary>
        /// 生成删除SQL
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="model"></param>
        private void GenerateDeleteSQL(DbCommand cmd, Object model)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                Attribute[] classAttributes = Attribute.GetCustomAttributes(model.GetType());
                if (classAttributes != null && classAttributes.Length > 0)
                {
                    TableAttribute classTableAttribute = (TableAttribute)classAttributes[0];
                    sb.Append("delete from ").Append(classTableAttribute.Tablename).Append(" where id>0");
                    Hashtable htable = new Hashtable();

                    PropertyInfo[] properties = model.GetType().GetProperties();
                    if (properties != null && properties.Length > 0)
                    {
                        foreach (PropertyInfo lopProperty in properties)
                        {
                            if (lopProperty.GetValue(model, null) != null)
                            {
                                Attribute[] fieldAttrs = Attribute.GetCustomAttributes(lopProperty);
                                if (fieldAttrs != null && fieldAttrs.Length > 0)
                                {
                                    TableAttribute fieldAttribute = (TableAttribute)fieldAttrs[0];
                                    if (fieldAttribute.Isdeletefield)
                                    {
                                        if (fieldAttribute.Columnname.Equals("id") && (long)lopProperty.GetValue(model, null) > 0)
                                        {
                                            sb.Append(" and ").Append(fieldAttribute.Columnname).Append("=@").Append(fieldAttribute.Columnname);
                                            htable.Add(fieldAttribute.Columnname, lopProperty.GetValue(model, null));
                                        }
                                        else if (!fieldAttribute.Columnname.Equals("id"))
                                        {
                                            sb.Append(" and ").Append(fieldAttribute.Columnname).Append("=@").Append(fieldAttribute.Columnname);
                                            htable.Add(fieldAttribute.Columnname, lopProperty.GetValue(model, null));
                                        }
                                    }
                                }
                            }
                        }

                        cmd.CommandText = sb.ToString();
                        this.AddCommandParamters(cmd, htable);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        protected int Delete(DbConnection conn, Object model)
        {
            int flag = 0;
            try
            {
                DbCommand cmd = CommandFactory.CreateCommand();
                cmd.Connection = conn;
                this.GenerateDeleteSQL(cmd, model);
                Console.WriteLine(cmd.CommandText);
                flag = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
    }
}
